﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.project.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.project.dao.*;
import com.hh.bss.project.entity.*;
import com.hh.bss.project.service.*;
import com.hh.bss.project.vo.query.*;
import com.hh.bss.talent.entity.Cv;
import com.hh.bss.talent.service.CvService;
import com.hh.bss.util.*;





import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class SearchProjectCvDaoImpl extends BaseHibernateDao<SearchProjectCv,java.lang.Integer>  implements SearchProjectCvDao<SearchProjectCv,java.lang.Integer>{
	private CvService cvService;
	public void setCvServiceImpl(CvService service) {
		this.cvService = service;
	}
	public Class getEntityClass() {
		return SearchProjectCv.class;
	}
	public Page findProjectCvPage(SearchProjectCvQuery query){
		
		//属于项目的人选，但是有履历的，不一定有CC记录
		StringBuffer sql = new StringBuffer();
		
		/*
		sql.append(" select * from ( ");
		sql.append(" select a.cv_id id,a.project_id projectId,a.company_id companyId,b1.call_dt callDt,a.status cvStatus,b2.name companyName,a.cv_id cvId,b.cv_name cvName,c.name deptName,d.name titleName,e.current_salary currentSalary,b.mobile,b.tel phone,b.email,b.sex,b.function,b.level,b.degree,b.work_year workYear,b.expect_location expectLocation,b.birthday,b.cv_tag cvTag,cv_fl_level cvFlLevel from (select distinct project_id,cv_id,company_id,dept_id,title_id,status from search_project_cv) a ");
		sql.append(" left join  cv b on a.cv_id=b.id ");
		sql.append(" left join  (select project_id,ref_cvid,max(call_dt) call_dt from   search_project_cc_list  group by project_id,ref_cvid) b1 on a.cv_id=b1.ref_cvid and a.project_id=b1.project_id ");
		sql.append(" left join  company b2 on a.company_id=b2.id ");
		sql.append(" left join company_dept c on a.dept_id=c.id ");
		sql.append(" left join company_dept_title d on a.title_id=d.id  ");
		sql.append(" left join cv_salary e on a.cv_id=e.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as mobile from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_MOBILE).append("' group by cv_id) f on a.cv_id=f.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((concat(if(nbr_ext1 is null,'','('),ifnull(nbr_ext1,''),if(nbr_ext1 is null,'',')'),ifnull(nbr,''),if(nbr_ext2 is null,'','-'),ifnull(nbr_ext2,''))) separator ',') as phone from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_TEL).append("' group by cv_id) h on a.cv_id=h.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as email from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_EMAIL).append("' group by cv_id) i on a.cv_id=i.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((tag) separator ',') as tag from cv_tag group by cv_id) j on a.cv_id=j.cv_id ");
		sql.append(" where a.project_id=? ) comcv ");
		*/
		
		//sql.append(" left join (select f.company_id,f.id custId,max(g.id) 
		//contractId from customer f left join customer_contract g on f.id=g.cust_id group by f.company_id,f.id)
		//h on a.company_id=h.company_id ");
		sql.append(" select * from ( ");
		sql.append(" select a.id id,a.project_id projectId,a.company_id companyId,max(c.call_dt) callDt,a.status cvStatus,b.company companyName,a.cv_id cvId,b.cv_name cvName,b.company,b.dept deptName,if(b.position <> '',b.position,b.title) titleName, ");
		sql.append(" d.current_salary currentSalary,b.current_address currentAddress,b.mobile,b.tel phone,b.email,b.sex,b.function,b.industry,b.level,b.degree,b.work_year workYear, b.expect_location expectLocation,b.register_location registerLocation,b.married,b.birthday,b.cv_tag cvTag,b.cv_fl_level cvFlLevel ,if(b.cv_text <> '',1,0) cvText , ");
		sql.append(" h.custId ,h.contractId,g.attachmentId ");  //2012年7月30日 11:45:55 alvin 简历人选标红
		sql.append("  from search_project_cv a inner join cv b on a.cv_id=b.id and b.is_delete=0 ");
		//sql.append(" left join company  e on b.company_id=e.id");
		sql.append(" left join search_project_cc_list c on c.project_id=? and a.cv_id=c.ref_cvid ");
		sql.append(" left join cv_salary d on a.cv_id=d.cv_id ");
		sql.append(" left join (select f.company_id,f.id custId,max(g.id) contractId from customer f inner join customer_contract g on f.id=g.cust_id group by f.company_id,f.id) h on b.company_id=h.company_id ");
		//有无简历 (文本简历 和附件)
		sql.append(" left join (select a.id attachmentId,a.cv_id  from cv_attachment a where a.type='R'  group by a.cv_id ) g on a.cv_id = g.cv_id  ");
		sql.append(" where a.project_id=? ");
		sql.append(" group by a.id ");
		sql.append(" ) comcv where 1=1 ");
		
		
		List values = new ArrayList();
		
		//sql.append(" where comcv.projectId=? ");
		
		System.out.println(sql.toString());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		
		if(isNotEmpty(query.getCompanyId())) {
 		   //sql.append(" and comcv.companyId=? ");
		   //并且是在公司的人选列表里
 		   sql.append(" and exists( select 1 from company_cv a where a.company_id=? and comcv.cvId=a.cv_id ) ");
 		   
		    values.add(query.getCompanyId());
		}
		
		//有cc
		if ("1".equals(query.getCvType())){
			sql.append(" and exists( select 1 from search_project_cc_list a where a.project_id=? and comcv.cvId=a.ref_cvid ) ");
		    values.add(query.getProjectId());
		}
		//有履历
		if ("2".equals(query.getCvType())){
		   sql.append(" and exists (select 1 from cv a where ( exists (select 1 from cv_education a1 where a.id=a1.cv_id) or  exists (select 1 from cv_certificate a2 where a.id=a2.cv_id) or  exists (select 1 from cv_trainning a3 where a.id=a3.cv_id) or  exists (select 1 from cv_work a4 where a.id=a4.cv_id) or exists (select 1 from cv_attachment b5 where a.id=b5.cv_id)  ) and comcv.cvId=a.id)  ");		    
		}
		
		if(isNotEmpty(query.getHasAttachment())){
			//有简历附件
			if( 1 == query.getHasAttachment()){
				log.info("有附件:"+query.getHasAttachment());
				sql.append(" and exists( select 1 from cv_attachment a where a.cv_id = comcv.cvId ) ");
			}
			if(-1 == query.getHasAttachment()){
				log.info("没有附件:"+query.getHasAttachment());
				sql.append(" and not exists( select 1 from cv_attachment a where a.cv_id = comcv.cvId ) ");
			}
		}
		if(isNotEmpty(query.getCvName())) {
			sql.append(" and comcv.cvName like ? ");
			values.add("%"+query.getCvName()+"%");
		}
		if(isNotEmpty(query.getPhone())) {
			sql.append(" and ( comcv.phone like ?  or  comcv.mobile like ? ) ");
			values.add("%"+query.getPhone()+"%");
			values.add("%"+query.getPhone()+"%");
		}
		//现任公司  
		if(isNotEmpty(query.getCompany())){
			sql.append(" and comcv.company like ? ");
			values.add("%"+query.getCompany()+"%");
		}
		//现任职位/岗位
		if(isNotEmpty(query.getPosition())){
			sql.append(" and  comcv.titleName  like ?  ");
			values.add("%"+query.getPosition()+"%");
		}
		//System.out.println("******************"+sql.toString());
//		if(isNotEmpty(query.getPhone())) {
//			sql.append(" and ( comcv.phone like ?  or  comcv.mobile like ? ) ");
//			values.add("%"+query.getPhone()+"%");
//			values.add("%"+query.getPhone()+"%");
//		}
		if(isNotEmpty(query.getEmail())) {
			sql.append(" and comcv.email like ? ");
			values.add("%"+query.getEmail()+"%");
		}
		//人选表标签
		if(isNotEmpty(query.getCvTag())){
			sql.append(" and comcv.cvTag = ? ");
			values.add(query.getCvTag());
		}
		//人选状态 / 人选进展
		if(isNotEmpty(query.getCvStatus())){
			sql.append(" and comcv.cvStatus = ? ");
			values.add(query.getCvStatus());
		}
		if(isNotEmpty(query.getFitJob())){
			sql.append(" and exists( select 1 from search_project_cv_crm a where  a.fit_job = ? and comcv.cvId=a.cv_id  ) ");
		    values.add(query.getFitJob());
		}
		if(isNotEmpty(query.getCvFlags())){
			sql.append(" and exists ( select 1 from search_project_cv_crm a where   comcv.cvId=a.cv_id and cv_flags in ("+query.getCvFlags()+")) ");
		}
			
		if(isNotEmpty(query.getSortColumns())){
			sql.append(" order by comcv.").append(query.getSortColumns());
		}else{
			sql.append(" order by comcv.id desc ");
		}
		//return super.getJdbcDao().createPage(query, values, mapper, pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber()); 
	}
	public Page findPage(SearchProjectCvQuery query) {
        //XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
        // [column]为字符串拼接, {column}为使用占位符. [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接 
        // [column] 为PageRequest的属性
        
		
		    Class[] os={SearchProjectCvQuery.class};
    		Class property= org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
        if (property!=null){
        	 if ("java.lang.String".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(),query.getQuery());
	    			
	    		}
	    		else if ("java.lang.Integer".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));
	    			
	    		}
	    		else if ("java.lang.Double".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
	    		}
	    		else if ("java.util.Date".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
	    		}
        }	 
        //生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
        StringBuilder sql2 = new StringBuilder("select t from SearchProjectCv t where 1=1 ");
        
        //关联
        if(isNotEmpty(query.getJobId())) {        	
        	sql2 = new StringBuilder("select t from SearchProjectCv t,ContractJobSearchProject jp where t.projectId=jp.projectId ");
            sql2.append(" and  jp.jobId = :jobId ");
        }
          if(isNotEmpty(query.getId())) {
            sql2.append(" and  t.id = :id ");
        }
          if(isNotEmpty(query.getProjectId())) {
            sql2.append(" and  t.projectId = :projectId ");
        }
          if(isNotEmpty(query.getCvId())) {
            sql2.append(" and  t.cvId = :cvId ");
        }
          if(isNotEmpty(query.getCompanyId())) {
            sql2.append(" and  t.companyId = :companyId ");
        }
        if(isNotEmpty(query.getCreator())) {
            sql2.append(" and  t.creator like :creator ");
            query.setCreator("%"+query.getCreator()+"%"); 

        }
        if(isNotEmpty(query.getCreateDtBegin())) {
            sql2.append(" and  t.createDt >= :createDtBegin ");
        }
        if(isNotEmpty(query.getCreateDtEnd())) {
            sql2.append(" and  t.createDt <= :createDtEnd ");
        }
        if(isNotEmpty(query.getSortColumns())) {
            sql2.append(" order by :sortColumns ");
        }	
        
		return pageQuery(sql2.toString(),query);
	}
	
	public boolean cvJoinProject(List<SearchProjectCv> projectCvs){
		
	    for(int i=0;i<projectCvs.size();i++){
	    	SearchProjectCv searchProjectCv=projectCvs.get(i);
	    	List values = new ArrayList();
	    	values.add(searchProjectCv.getProjectId());
	    	values.add(searchProjectCv.getCvId());
	    	List l = this.getHibernateTemplate().find("from SearchProjectCv a where a.projectId=? and a.cvId=?", values.toArray());
	    	if (l!=null&&l.size()>0) continue;
	    	
	    	Cv cv=cvService.getById(searchProjectCv.getCvId());
	    	//如果已经存在，则不处理
	    	
	    	
	    	//if (cvService.isProtectCv(searchProjectCv.getCvId()))  throw new RuntimeException("人选["+cv.getCvName()+"]在客户公司就职，无法添加为目标人选.");
	    	//this.saveAll(projectCvs);
	    	this.save(searchProjectCv);
	    	//System.out.println("%%%%%%%%%%%%%%%%%%%%%%%%%"+i);
	    	this.flush();
	    }
		return true;
	}
	@SuppressWarnings("unchecked")
	
	public SearchProjectInterview getSearchProjectInterviewById(Integer id) {
		StringBuffer sql=new StringBuffer();
		List values=new ArrayList();
		sql.append(" select a.id,a.cv_id cvId,a.project_id projectId,a.interviewer,a.plan_dt planDt,a.iv_dt ivDt,a.address,a.result,a.content,cv_name cvName from search_project_interview a , cv b where a.cv_id = b.id and a.id = ").append(id);
		//SearchProjectInterview  interview = new SearchProjectInterview();
		SearchProjectInterview searchProjectInterview = this.getJdbcDao().getJdbcTemplate().queryForObject(sql.toString(), new MapObjectRowMapper(){
			@Override
			public Object mapRow(ResultSet rs, int row) throws SQLException {
				SearchProjectInterview  interview = new SearchProjectInterview();
				interview.setId(rs.getInt("id"));
				interview.setCvId(rs.getInt("cvId"));
				interview.setProjectId(rs.getInt("projectId"));
				interview.setInterviewer(rs.getString("interviewer"));
				interview.setPlanDt(rs.getDate("planDt"));
				interview.setPlanDtStr(rs.getString("planDt"));
				interview.setPlanDtString(rs.getString("planDt"));
				interview.setIvDt(rs.getDate("ivDt"));
				interview.setIvDtStr(rs.getString("ivDt"));
				interview.setIvDtString(rs.getString("ivDt"));
				interview.setContent(rs.getString("content"));
				interview.setAddress(rs.getString("address"));
				interview.setResult(rs.getInt("result"));
				interview.setCvName(rs.getString("cvName"));
				return  interview;
			}
		});
		return searchProjectInterview;
	}

	
	

}
